﻿using MySql.Data.MySqlClient;
using SGV.Datos.Interfases;
using SGV.Entidad;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Linq.Expressions;
using System.Text;
using System.Threading.Tasks;
using Dapper;
using DapperExtensions;

namespace SGV.Datos.Repositories
{
    public class ClienteRepository : IClienteRepository
    {

        private IDbConnection _Db;

        public ClienteRepository()
        {
            //constructor
        }

        public IList<Cliente> FindAll()
        {
            string query = @"SELECT * FROM cliente INNER JOIN localidad ON localidad.id = cliente.locid
                                    INNER JOIN tipoiva ON tipoiva.id = cliente.tpiid";

            using (_Db = new MySqlConnection(ConfigurationManager.ConnectionStrings["MySQL"].ToString()))
            {
                return _Db.Query<Cliente, Localidad, TipoIva, Cliente>(query, (c, l, t) => 
                { 
                    c.localidad = l; 
                    c.tipoiva = t; 
                    return c; }).ToList();
            }
        }

        public IQueryable<Cliente> Find(Expression<Func<Cliente, bool>> predicate)
        {
            throw new NotImplementedException();
        }

        public Cliente FindById(int id)
        {
            string query = "SELECT * " +
                            "FROM cliente " +
                            "INNER JOIN tipoiva ON tipoiva.id = cliente.tpiid " + 
                            "WHERE cliente.id = " + id;

            Cliente entidad = new Cliente();

            using (_Db = new MySqlConnection(ConfigurationManager.ConnectionStrings["MySQL"].ToString()))
            {
                try
                {


                    entidad = _Db.Query<Cliente, TipoIva, Cliente>(query, (cliente, iva) => { cliente.tipoiva = iva; return cliente; }).SingleOrDefault();
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }

            return entidad;
        }

        public int add(Cliente newEntity)
        {
            string query = "INSERT INTO cliente (nombreapellido, dni, direccion," + 
                                                        "barrio, locid, cp, telefono, email," +
                                                        "tpiid, cuit, ingbrutos, observaciones," +
                                                        "fechaalta) " +
                                        "VALUES (@nombreapellido, @dni, @direccion, @barrio," +
                                                "@locid, @cp, @telefono, @email, @tpiid, @cuit," +
                                                "@ingbrutos, @observaciones, @fechaalta);" +
                                        "SELECT CAST(LAST_INSERT_ID() AS UNSIGNED INTEGER);";

            try
            {
                using (_Db = new MySqlConnection(ConfigurationManager.ConnectionStrings["MySQL"].ToString()))
                {
                    var id = _Db.Query<ulong>(query, new
                    {
                        newEntity.nombreapellido,
                        newEntity.dni,
                        newEntity.direccion,
                        newEntity.barrio,
                        locid = newEntity.localidad.id,
                        newEntity.cp,
                        newEntity.telefono,
                        newEntity.email,
                        tpiid = newEntity.tipoiva.id,
                        newEntity.cuit,
                        newEntity.ingbrutos,
                        newEntity.observaciones,
                        newEntity.fechaalta,
                    }).Single();

                    return Convert.ToInt32(id);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }

           
        }

        public bool remove(int id)
        {
            throw new NotImplementedException();
        }

        public int Modify(Cliente entity)
        {
            throw new NotImplementedException();
        }
    }
}
